package com.infodms.dms.dao.claim.laborList;

import java.sql.ResultSet;
import java.util.List;
import java.util.Map;

import com.infodms.dms.bean.SummaryReportBean;
import com.infodms.dms.dao.common.BaseDao;
import com.infodms.dms.util.StringUtil;
import com.infoservice.po3.bean.PO;
import com.infoservice.po3.bean.PageResult;

public class LaborListSummaryReportDao extends BaseDao {
	private LaborListSummaryReportDao(){}
	public static LaborListSummaryReportDao getInstance(){
		return new LaborListSummaryReportDao();
	}
	@Override
	protected PO wrapperPO(ResultSet rs, int idx) {
		// TODO Auto-generated method stub
		return null;
	}

	/*
	 * 劳务清单汇总报表主页面主查询
	 */
	@SuppressWarnings("unchecked")
	public PageResult<Map<String,Object>> mainQuery(String con,int pageSize,int curPage){
		StringBuffer sql = new StringBuffer("\n");
		sql.append("select s.sum_parameter_id,\n" );
		sql.append("       s.sum_parameter_no,\n" );
		sql.append("       s.taxable_service_sum_id,\n" );
		sql.append("       s.purchaser_id,\n" );
		sql.append("       s.purchaser_name,\n" );
		sql.append("       s.sales_id,\n" );
		sql.append("       d.dealer_code as dealer_code,\n");
		sql.append("       d.dealer_name,\n" );
		sql.append("       s.invoice_no,\n" );
		sql.append("       s.tax_rate,\n" );
		sql.append("       s.oem_company_id,\n" );
		sql.append("       s.statistics_date, \n" );
		sql.append("       s.auth_status,s.amount \n" );
		sql.append("  from tt_taxable_service_sum s,tm_dealer d\n" );
		sql.append(" where s.dlr_id = d.dealer_id\n");
		if(StringUtil.notNull(con))
			sql.append(con);
		sql.append("  order by s.sum_parameter_no desc\n");
		return this.pageQuery(sql.toString(), null, this.getFunName(), pageSize, curPage);
	}
	
	/*
	 * 劳务清单参数设置弹出框查询方法
	 */
	@SuppressWarnings("unchecked")
	public PageResult<Map<String,Object>> getReport(String con,int pageSize,int curPage){
		StringBuilder sql= new StringBuilder("\n");
		sql.append("select l.report_id,\n" );
		sql.append("       l.report_code,\n" );
		sql.append("       l.dealer_id,\n" );
		sql.append("       l.yieldly,\n" );
		sql.append("       l.receive_date,\n" );
		sql.append("       l.receive_man,\n" );
		sql.append("       l.amount,\n" );
		sql.append("       l.invoice_code,\n" );
		sql.append("       l.make_man,\n" );
		sql.append("       l.status,\n" );
		sql.append("       d.dealer_code,\n");
		sql.append("       d.dealer_name\n");
		sql.append("  from tt_labor_list l,tm_dealer d\n" );
		sql.append(" where l.dealer_id=d.dealer_id\n");
		if(StringUtil.notNull(con))
			sql.append(con);
		sql.append(" order by report_code desc");
		return this.pageQuery(sql.toString(), null, this.getFunName(), pageSize, curPage);
	}
	
	/*
	 * 针对生成按钮执行的查询操作
	 */
	@SuppressWarnings("unchecked")
	public List<SummaryReportBean> getMyMap(String code){
		StringBuilder sql= new StringBuilder("\n");
		/*sql.append("select '1' as ord,'走保费' as type,MODEL_CODE, sum(BALANCE_AMOUNT) balance_amount\n" );
		sql.append("  from tt_as_wr_application\n" );
		sql.append(" where claim_type = 10661002\n" );
		sql.append("   and id in\n" );
		sql.append("       (select claim_id\n" );
		sql.append("          from TR_BALANCE_CLAIM\n" );
		sql.append("         where balance_id in\n" );
		sql.append("               (select id\n" );
		sql.append("                  from TT_AS_WR_CLAIM_BALANCE\n" );
		sql.append("                 where BALANCE_NO in\n" );
		sql.append("                       (SELECT BALANCE_NO\n" );
		sql.append("                          FROM TT_LABOR_LIST_DETAIL\n" );
		sql.append("                         where report_id in\n" );
		sql.append("                               (SELECT report_id\n" );
		sql.append("                                  FROM TT_LABOR_LIST\n" );
		sql.append("                                 WHERE REPORT_CODE = '").append(code).append("'))))\n" );
		sql.append(" GROUP BY MODEL_CODE\n" );
		sql.append(" union\n" );
		sql.append("select '2' as ord,'工时费', MODEL_CODE,sum(LABOUR_AMOUNT) from tt_as_wr_application\n" );
		sql.append("where  id in(\n" );
		sql.append("select claim_id\n" );
		sql.append("          from TR_BALANCE_CLAIM\n" );
		sql.append("         where balance_id in\n" );
		sql.append("               (select id\n" );
		sql.append("                  from TT_AS_WR_CLAIM_BALANCE\n" );
		sql.append("                 where BALANCE_NO in\n" );
		sql.append("                       (SELECT BALANCE_NO\n" );
		sql.append("                          FROM TT_LABOR_LIST_DETAIL\n" );
		sql.append("                         where report_id in\n" );
		sql.append("                               (SELECT report_id\n" );
		sql.append("                                  FROM TT_LABOR_LIST\n" );
		sql.append("                                 WHERE REPORT_CODE = '").append(code).append("'))))\n" );
		sql.append("GROUP BY MODEL_CODE\n" );
		sql.append("union\n" );
		sql.append("select '3' as ord,'材料费', MODEL_CODE,sum(PART_AMOUNT) from tt_as_wr_application where\n" );
		sql.append("id in (\n" );
		sql.append("select claim_id\n" );
		sql.append("          from TR_BALANCE_CLAIM\n" );
		sql.append("         where balance_id in\n" );
		sql.append("               (select id\n" );
		sql.append("                  from TT_AS_WR_CLAIM_BALANCE\n" );
		sql.append("                 where BALANCE_NO in\n" );
		sql.append("                       (SELECT BALANCE_NO\n" );
		sql.append("                          FROM TT_LABOR_LIST_DETAIL\n" );
		sql.append("                         where report_id in\n" );
		sql.append("                               (SELECT report_id\n" );
		sql.append("                                  FROM TT_LABOR_LIST\n" );
		sql.append("                                 WHERE REPORT_CODE = '").append(code).append("'))))\n" );
		sql.append(" GROUP BY MODEL_CODE\n" );
		sql.append(" union\n" );
		sql.append(" select '4' as ord,'救急费','',sum(SPEOUTFEE_AMOUNT+OTHER_AMOUNT)  from TT_AS_WR_CLAIM_BALANCE\n" );
		sql.append(" where BALANCE_NO in\n" );
		sql.append("       (SELECT BALANCE_NO\n" );
		sql.append("          FROM TT_LABOR_LIST_DETAIL\n" );
		sql.append("         where report_id in\n" );
		sql.append("               (SELECT report_id\n" );
		sql.append("                  FROM TT_LABOR_LIST\n" );
		sql.append("                 WHERE REPORT_CODE='").append(code).append("'))\n" );
		sql.append("union\n" );
		sql.append(" select '5' as ord ,'三包经济补偿费','',sum(SERVICE_TOTAL_AMOUNT) from TT_AS_WR_CLAIM_BALANCE\n" );
		sql.append(" where BALANCE_NO in\n" );
		sql.append("       (SELECT BALANCE_NO\n" );
		sql.append("          FROM TT_LABOR_LIST_DETAIL\n" );
		sql.append("         where report_id in\n" );
		sql.append("               (SELECT report_id\n" );
		sql.append("                  FROM TT_LABOR_LIST\n" );
		sql.append("                 WHERE REPORT_CODE='").append(code).append("'))\n" );
		sql.append("union\n" );
		sql.append("select '6' as ord ,'旧件运费 ','',SUM(RETURN_AMOUNT)\n" );
		sql.append("  from TT_AS_WR_CLAIM_BALANCE\n" );
		sql.append(" where BALANCE_NO in\n" );
		sql.append("       (SELECT BALANCE_NO\n" );
		sql.append("          FROM TT_LABOR_LIST_DETAIL\n" );
		sql.append("         where report_id in\n" );
		sql.append("               (SELECT report_id\n" );
		sql.append("                  FROM TT_LABOR_LIST\n" );
		sql.append("                 WHERE REPORT_CODE='").append(code).append("'))\n" );
		sql.append("union\n" );
		sql.append("select '7' as ord ,'三包维修折让','',sum(market_amount+ APPEND_AMOUNT+APPEND_LABOUR_AMOUNT)\n" );
		sql.append("from TT_AS_WR_CLAIM_BALANCE\n" );
		sql.append("where BALANCE_NO in\n" );
		sql.append("       (SELECT BALANCE_NO\n" );
		sql.append("          FROM TT_LABOR_LIST_DETAIL\n" );
		sql.append("         where report_id in\n" );
		sql.append("               (SELECT report_id\n" );
		sql.append("                  FROM TT_LABOR_LIST\n" );
		sql.append("                 WHERE REPORT_CODE='").append(code).append("'))\n" );
		sql.append("union\n" );
		sql.append("select '8' as ord,'财务扣款','',sum(OLD_DEDUCT+ SERVICE_DEDUCT+FREE_DEDUCT+CHECK_DEDUCT +ADMIN_DEDUCT+FINANCIAL_DEDUCT)\n" );
		sql.append("from TT_AS_WR_CLAIM_BALANCE\n" );
		sql.append("where BALANCE_NO in\n" );
		sql.append("       (SELECT BALANCE_NO\n" );
		sql.append("          FROM TT_LABOR_LIST_DETAIL\n" );
		sql.append("         where report_id in\n" );
		sql.append("               (SELECT report_id\n" );
		sql.append("                  FROM TT_LABOR_LIST\n" );
		sql.append("                 WHERE REPORT_CODE='").append(code).append("'))\n" );
		sql.append("union\n" );
		sql.append("select '9' as ord,'合计','',sum(BALANCE_AMOUNT) - sum(FINANCIAL_DEDUCT)\n" );
		sql.append("from TT_AS_WR_CLAIM_BALANCE\n" );
		sql.append("where BALANCE_NO in\n" );
		sql.append("       (SELECT BALANCE_NO\n" );
		sql.append("          FROM TT_LABOR_LIST_DETAIL\n" );
		sql.append("         where report_id in\n" );
		sql.append("               (SELECT report_id\n" );
		sql.append("                  FROM TT_LABOR_LIST\n" );
		sql.append("                 WHERE REPORT_CODE='").append(code).append("'))\n" );*/
		sql.append("\n");
		sql.append("select '1' as ord,\n");  
		sql.append("       '走保费' as type,\n");  
		sql.append("       c.group_code model_code,\n");  
		sql.append("       sum(BALANCE_AMOUNT) balance_amount\n");  
		sql.append("  from tt_as_wr_application   t,\n");  
		sql.append("       tm_vhcl_material_group b,\n");  
		sql.append("       tm_vhcl_material_group C\n");  
		sql.append(" where claim_type = 10661002\n");  
		sql.append("   and t.MODEL_CODE = B.GROUP_CODE\n");  
		sql.append("   AND B.PARENT_GROUP_ID = C.GROUP_ID\n");  
		sql.append("   and id in\n");  
		sql.append("       (select claim_id\n");  
		sql.append("          from TR_BALANCE_CLAIM\n");  
		sql.append("         where balance_id in\n");  
		sql.append("               (select id\n");  
		sql.append("                  from TT_AS_WR_CLAIM_BALANCE\n");  
		sql.append("                 where BALANCE_NO in\n");  
		sql.append("                       (SELECT BALANCE_NO\n");  
		sql.append("                          FROM TT_LABOR_LIST_DETAIL\n");  
		sql.append("                         where report_id in\n");  
		sql.append("                               (SELECT report_id\n");  
		sql.append("                                  FROM TT_LABOR_LIST\n");  
		sql.append("                                 WHERE REPORT_CODE = '"+code+"'))))\n");  
		sql.append(" GROUP BY c.group_code\n");  
		sql.append("union\n");  
		sql.append("select '2' as ord, '工时费', c.group_code model_code, sum(LABOUR_AMOUNT)\n");  
		sql.append("  from tt_as_wr_application t,\n");  
		sql.append("       tm_vhcl_material_group b,\n");  
		sql.append("       tm_vhcl_material_group C\n");  
		sql.append(" where t.MODEL_CODE = B.GROUP_CODE\n");  
		sql.append("   AND B.PARENT_GROUP_ID = C.GROUP_ID\n");  
		sql.append("   and id in\n");  
		sql.append("       (select claim_id\n");  
		sql.append("          from TR_BALANCE_CLAIM\n");  
		sql.append("         where balance_id in\n");  
		sql.append("               (select id\n");  
		sql.append("                  from TT_AS_WR_CLAIM_BALANCE\n");  
		sql.append("                 where BALANCE_NO in\n");  
		sql.append("                       (SELECT BALANCE_NO\n");  
		sql.append("                          FROM TT_LABOR_LIST_DETAIL\n");  
		sql.append("                         where report_id in\n");  
		sql.append("                               (SELECT report_id\n");  
		sql.append("                                  FROM TT_LABOR_LIST\n");  
		sql.append("                                 WHERE REPORT_CODE = '"+code+"'))))\n");  
		sql.append(" GROUP BY c.group_code\n");  
		sql.append("union\n");  
		sql.append("select '3' as ord, '材料费', c.group_code model_code, sum(PART_AMOUNT)\n");  
		sql.append("  from tt_as_wr_application   t,\n");  
		sql.append("       tm_vhcl_material_group b,\n");  
		sql.append("       tm_vhcl_material_group C\n");  
		sql.append(" where t.MODEL_CODE = B.GROUP_CODE\n");  
		sql.append("   AND B.PARENT_GROUP_ID = C.GROUP_ID\n");  
		sql.append("   and id in\n");  
		sql.append("       (select claim_id\n");  
		sql.append("          from TR_BALANCE_CLAIM\n");  
		sql.append("         where balance_id in\n");  
		sql.append("               (select id\n");  
		sql.append("                  from TT_AS_WR_CLAIM_BALANCE\n");  
		sql.append("                 where BALANCE_NO in\n");  
		sql.append("                       (SELECT BALANCE_NO\n");  
		sql.append("                          FROM TT_LABOR_LIST_DETAIL\n");  
		sql.append("                         where report_id in\n");  
		sql.append("                               (SELECT report_id\n");  
		sql.append("                                  FROM TT_LABOR_LIST\n");  
		sql.append("                                 WHERE REPORT_CODE = '"+code+"'))))\n");  
		sql.append(" GROUP BY c.group_code\n");  
		sql.append("union\n");  
		sql.append("select '4' as ord, '救急费', '', sum(SPEOUTFEE_AMOUNT + OTHER_AMOUNT)\n");  
		sql.append("  from TT_AS_WR_CLAIM_BALANCE\n");  
		sql.append(" where BALANCE_NO in\n");  
		sql.append("       (SELECT BALANCE_NO\n");  
		sql.append("          FROM TT_LABOR_LIST_DETAIL\n");  
		sql.append("         where report_id in\n");  
		sql.append("               (SELECT report_id\n");  
		sql.append("                  FROM TT_LABOR_LIST\n");  
		sql.append("                 WHERE REPORT_CODE = '"+code+"'))\n");  
		sql.append("union\n");  
		sql.append("select '5' as ord, '三包经济补偿费', '', sum(SERVICE_TOTAL_AMOUNT)\n");  
		sql.append("  from TT_AS_WR_CLAIM_BALANCE\n");  
		sql.append(" where BALANCE_NO in\n");  
		sql.append("       (SELECT BALANCE_NO\n");  
		sql.append("          FROM TT_LABOR_LIST_DETAIL\n");  
		sql.append("         where report_id in\n");  
		sql.append("               (SELECT report_id\n");  
		sql.append("                  FROM TT_LABOR_LIST\n");  
		sql.append("                 WHERE REPORT_CODE = '"+code+"'))\n");  
		sql.append("union\n");  
		sql.append("select '6' as ord, '旧件运费 ', '', SUM(RETURN_AMOUNT)\n");  
		sql.append("  from TT_AS_WR_CLAIM_BALANCE\n");  
		sql.append(" where BALANCE_NO in\n");  
		sql.append("       (SELECT BALANCE_NO\n");  
		sql.append("          FROM TT_LABOR_LIST_DETAIL\n");  
		sql.append("         where report_id in\n");  
		sql.append("               (SELECT report_id\n");  
		sql.append("                  FROM TT_LABOR_LIST\n");  
		sql.append("                 WHERE REPORT_CODE = '"+code+"'))\n");  
		sql.append("union\n");  
		sql.append("select '7' as ord,\n");  
		sql.append("       '三包维修折让',\n");  
		sql.append("       '',\n");  
		sql.append("       sum(market_amount + APPEND_AMOUNT + APPEND_LABOUR_AMOUNT)\n");  
		sql.append("  from TT_AS_WR_CLAIM_BALANCE\n");  
		sql.append(" where BALANCE_NO in\n");  
		sql.append("       (SELECT BALANCE_NO\n");  
		sql.append("          FROM TT_LABOR_LIST_DETAIL\n");  
		sql.append("         where report_id in\n");  
		sql.append("               (SELECT report_id\n");  
		sql.append("                  FROM TT_LABOR_LIST\n");  
		sql.append("                 WHERE REPORT_CODE = '"+code+"'))\n");  
		sql.append("union\n");  
		sql.append("select '8' as ord,\n");  
		sql.append("       '财务扣款',\n");  
		sql.append("       '',\n");  
		sql.append("       sum(OLD_DEDUCT + SERVICE_DEDUCT + FREE_DEDUCT + CHECK_DEDUCT +\n");  
		sql.append("           ADMIN_DEDUCT + FINANCIAL_DEDUCT)\n");  
		sql.append("  from TT_AS_WR_CLAIM_BALANCE\n");  
		sql.append(" where BALANCE_NO in\n");  
		sql.append("       (SELECT BALANCE_NO\n");  
		sql.append("          FROM TT_LABOR_LIST_DETAIL\n");  
		sql.append("         where report_id in\n");  
		sql.append("               (SELECT report_id\n");  
		sql.append("                  FROM TT_LABOR_LIST\n");  
		sql.append("                 WHERE REPORT_CODE = '"+code+"'))\n");  
		sql.append("union\n");  
		sql.append("select '9' as ord, '合计', '', sum(BALANCE_AMOUNT) - sum(FINANCIAL_DEDUCT)\n");  
		sql.append("  from TT_AS_WR_CLAIM_BALANCE\n");  
		sql.append(" where BALANCE_NO in\n");  
		sql.append("       (SELECT BALANCE_NO\n");  
		sql.append("          FROM TT_LABOR_LIST_DETAIL\n");  
		sql.append("         where report_id in\n");  
		sql.append("               (SELECT report_id\n");  
		sql.append("                  FROM TT_LABOR_LIST\n");  
		sql.append("                 WHERE REPORT_CODE = '"+code+"'))\n");  

		return this.select(SummaryReportBean.class, sql.toString(), null);
	}
	
	/*
	 * OEM 明细按钮查询方法  主数据
	 */
	@SuppressWarnings("unchecked")
	public PageResult<Map<String,Object>> getDetail(String id){
		StringBuilder sql= new StringBuilder("\n");
		sql.append("select s.taxable_service_sum_id,\n" );
		sql.append("       s.sum_parameter_id,\n" );
		sql.append("       s.sum_parameter_no,\n" );
		sql.append("       s.purchaser_id,\n" );
		sql.append("       s.sales_id,\n" );
		sql.append("       s.invoice_no,\n" );
		sql.append("       s.tax_rate,\n" );
		sql.append("       d.dealer_code,\n" );
		sql.append("       d.dealer_name\n" );
		sql.append("  from tt_taxable_service_sum s,tm_dealer d\n" );
		sql.append(" where s.dlr_id = d.dealer_id\n");
		sql.append("   and s.sum_parameter_id=").append(id).append("\n");
		return this.pageQuery(sql.toString(),null, this.getFunName(), 2, 1);
	}
	
	/*
	 * SQL
	 */
	public void insertPrintDetail(Long id){
		StringBuffer sql = new StringBuffer("\n");
		sql.append("insert into tt_taxable_sum_print_tmp\n");
		sql.append("  (id, list_id, order_type, order_desc, model_code, balance_amount)\n");  
		sql.append("  select * from (WITH ININ AS (SELECT TBC.CLAIM_ID\n");  
		sql.append("                          FROM TR_BALANCE_CLAIM TBC\n");  
		sql.append("                         WHERE EXISTS\n");  
		sql.append("                         (SELECT 1\n");  
		sql.append("                                  FROM TT_AS_WR_CLAIM_BALANCE TAWCB\n");  
		sql.append("                                 WHERE EXISTS\n");  
		sql.append("                                 (SELECT 1\n");  
		sql.append("                                          FROM TT_LABOR_LIST_DETAIL TLLD,\n");  
		sql.append("                                               TT_LABOR_LIST        TLL\n");  
		sql.append("                                         WHERE TLLD.REPORT_ID = TLL.REPORT_ID\n");  
		sql.append("                                           AND TLL.REPORT_id = "+id+"\n");  
		sql.append("                                           AND TLLD.BALANCE_NO =\n");  
		sql.append("                                               TAWCB.BALANCE_NO)\n");  
		sql.append("                                   AND TAWCB.ID = TBC.BALANCE_ID))\n");  
		sql.append("\n");  
		sql.append("           select f_getid,\n");  
		sql.append("                  '"+id+"',\n");  
		sql.append("                  '1' as ord,\n");  
		sql.append("                  '走保费' as type,\n");  
		sql.append("                  T.MODEL_CODE model_code,\n");  
		sql.append("                  sum(BALANCE_AMOUNT) balance_amount\n");  
		sql.append("             from tt_as_wr_application t\n");  
		sql.append("            where claim_type = 10661002\n");  
		sql.append("              AND EXISTS\n");  
		sql.append("            (SELECT 1 FROM ININ WHERE ININ.CLAIM_ID = T.ID)\n");  
		sql.append("            GROUP BY T.MODEL_CODE\n");  
		sql.append("           union\n");  
		sql.append("           select f_getid,\n");  
		sql.append("                  '"+id+"',\n");  
		sql.append("                  '2' as ord,\n");  
		sql.append("                  '工时费',\n");  
		sql.append("                  T.MODEL_CODE model_code,\n");  
		sql.append("                  sum(nvl(T.BALANCE_LABOUR_AMOUNT,0)+nvl(T.Append_Amount,0) +nvl(T.Appendlabour_Amount,0))  \n");  
		sql.append("             from tt_as_wr_application t\n");  
		sql.append("            where T.CLAIM_TYPE IN (10661001, 10661007, 10661009)\n");  
		sql.append("              AND EXISTS\n");  
		sql.append("            (SELECT 1 FROM ININ WHERE ININ.CLAIM_ID = T.ID)\n");  
		sql.append("            GROUP BY T.MODEL_CODE\n");  
		sql.append("\n");  
		sql.append("           union\n");  
		sql.append("\n");  
		sql.append("           select f_getid,\n");  
		sql.append("                  '"+id+"',\n");  
		sql.append("                  '3' as ord,\n");  
		sql.append("                  '材料费',\n");  
		sql.append("                  T.MODEL_CODE model_code,\n");  
		sql.append("                  sum(T.BALANCE_PART_AMOUNT)\n");  
		sql.append("             from tt_as_wr_application t\n");  
		sql.append("            where T.CLAIM_TYPE IN (10661001, 10661007, 10661009)\n");  
		sql.append("              AND EXISTS\n");  
		sql.append("            (SELECT 1 FROM ININ WHERE ININ.CLAIM_ID = T.ID)\n");  
		sql.append("            GROUP BY T.MODEL_CODE\n");  
		sql.append("\n");  
		sql.append("           union\n");  
		sql.append("\n");  
		sql.append("           select f_getid,\n");  
		sql.append("                  '"+id+"',\n");  
		sql.append("                  '4' as ord,\n");  
		sql.append("                  '救急费',\n");  
		sql.append("                  '',\n");  
		sql.append("                  SUM(TAWCB.SPEOUTFEE_AMOUNT + TAWCB.OTHER_AMOUNT)\n");  
		sql.append("             from TT_AS_WR_CLAIM_BALANCE TAWCB\n");  
		sql.append("            where EXISTS (SELECT 1\n");  
		sql.append("                     FROM TT_LABOR_LIST_DETAIL TLLD, TT_LABOR_LIST TLL\n");  
		sql.append("                    WHERE TLLD.REPORT_ID = TLL.REPORT_ID\n");  
		sql.append("                      AND TLL.REPORT_id = "+id+"\n");  
		sql.append("                      AND TLLD.BALANCE_ID = TAWCB.ID)\n");  
		sql.append("\n");  
		sql.append("           union\n");  
		sql.append("\n");  
		sql.append("           select f_getid,\n");  
		sql.append("                  '"+id+"',\n");  
		sql.append("                  '7' as ord,\n");  
		sql.append("                  '三包维修折让',\n");  
		sql.append("                  '',\n");  
		sql.append("                  sum(TAWCB.SERVICE_TOTAL_AMOUNT)\n");  
		sql.append("             from TT_AS_WR_CLAIM_BALANCE TAWCB\n");  
		sql.append("            where EXISTS (SELECT 1\n");  
		sql.append("                     FROM TT_LABOR_LIST_DETAIL TLLD, TT_LABOR_LIST TLL\n");  
		sql.append("                    WHERE TLLD.REPORT_ID = TLL.REPORT_ID\n");  
		sql.append("                      AND TLL.REPORT_id = "+id+"\n");  
		sql.append("                      AND TLLD.BALANCE_ID = TAWCB.ID)\n");  
		sql.append("\n");  
		sql.append("           union\n");  
		sql.append("\n");  
		sql.append("           select f_getid,\n");  
		sql.append("                  '"+id+"',\n");  
		sql.append("                  '5' as ord,\n");  
		sql.append("                  '运费 ',\n");  
		sql.append("                  '',\n");  
		sql.append("                  SUM(TAWCB.RETURN_AMOUNT_BAK)\n");  
		sql.append("             from TT_AS_WR_CLAIM_BALANCE TAWCB\n");  
		sql.append("            where EXISTS (SELECT 1\n");  
		sql.append("                     FROM TT_LABOR_LIST_DETAIL TLLD, TT_LABOR_LIST TLL\n");  
		sql.append("                    WHERE TLLD.REPORT_ID = TLL.REPORT_ID\n");  
		sql.append("                      AND TLL.REPORT_id = "+id+"\n");  
		sql.append("                      AND TLLD.BALANCE_ID = TAWCB.ID)\n");  
		sql.append("\n");  
		sql.append("           union\n");  
		sql.append("\n");  
		sql.append("           select f_getid,\n");  
		sql.append("                  '"+id+"',\n");  
		sql.append("                  '6' as ord,\n");  
		sql.append("                  '三包经济补偿',\n");  
		sql.append("                  '',\n");  
		sql.append("                  sum(TAWCB.market_amount\n");  
		sql.append("                   )\n");  
		sql.append("             from TT_AS_WR_CLAIM_BALANCE TAWCB\n");  
		sql.append("            where EXISTS (SELECT 1\n");  
		sql.append("                     FROM TT_LABOR_LIST_DETAIL TLLD, TT_LABOR_LIST TLL\n");  
		sql.append("                    WHERE TLLD.REPORT_ID = TLL.REPORT_ID\n");  
		sql.append("                      AND TLL.REPORT_id = "+id+"\n");  
		sql.append("                      AND TLLD.BALANCE_ID = TAWCB.ID)\n");  
		sql.append("\n");  
		sql.append("           union\n");  
		sql.append("\n");  
		sql.append("           select f_getid,\n");  
		sql.append("                  '"+id+"',\n");  
		sql.append("                  '8' as ord,\n");  
		sql.append("                  '审核扣款',\n");  
		sql.append("                  '',\n");  
		sql.append("                  sum(TAWCB.OLD_DEDUCT + TAWCB.SERVICE_DEDUCT +\n");  
		sql.append("                      TAWCB.FREE_DEDUCT + TAWCB.CHECK_DEDUCT +\n");  
		sql.append("                      TAWCB.ADMIN_DEDUCT + TAWCB.FINANCIAL_DEDUCT + TAWCB.RETURN_AMOUNT_BAK - TAWCB.RETURN_AMOUNT)\n");  
		sql.append("             from TT_AS_WR_CLAIM_BALANCE TAWCB\n");  
		sql.append("            where EXISTS (SELECT 1\n");  
		sql.append("                     FROM TT_LABOR_LIST_DETAIL TLLD, TT_LABOR_LIST TLL\n");  
		sql.append("                    WHERE TLLD.REPORT_ID = TLL.REPORT_ID\n");  
		sql.append("                      AND TLL.REPORT_id = "+id+"\n");  
		sql.append("                      AND TLLD.BALANCE_ID = TAWCB.ID)\n");  
		sql.append("           union\n");  
		sql.append("\n");  
		sql.append("           select f_getid,\n");  
		sql.append("                  '"+id+"',\n");  
		sql.append("                  '9' as ord,\n");  
		sql.append("                  '总计',\n");  
		sql.append("                  '',\n");  
		sql.append("                  sum(TAWCB.BALANCE_AMOUNT) - sum(TAWCB.FINANCIAL_DEDUCT)\n");  
		sql.append("             from TT_AS_WR_CLAIM_BALANCE TAWCB\n");  
		sql.append("            where EXISTS (SELECT 1\n");  
		sql.append("                     FROM TT_LABOR_LIST_DETAIL TLLD, TT_LABOR_LIST TLL\n");  
		sql.append("                    WHERE TLLD.REPORT_ID = TLL.REPORT_ID\n");  
		sql.append("                      AND TLL.REPORT_id = "+id+"\n");  
		sql.append("                      AND TLLD.BALANCE_ID = TAWCB.ID))\n");
		this.insert(sql.toString());
	}
	
	/*
	 * 查询通过存储过程写进去的值以供打印显示
	 */
	public List<SummaryReportBean> getPrintDetail(Long id){
		StringBuffer sql = new StringBuffer();
		sql.append("select t.order_type ord,\n");
		sql.append("       t.order_desc type,\n");  
		sql.append("       GG.GROUP_NAME model_code,\n");  
		sql.append("       sum(balance_amount) balance_amount\n");  
		sql.append("  from tt_taxable_sum_print_tmp t, TM_VHCL_MATERIAL_GROUP G ,TM_VHCL_MATERIAL_GROUP GG\n");  
		sql.append(" where t.model_code = g.group_code(+)\n");  
		sql.append("   and t.list_id = "+id+"\n");  
		sql.append("   AND G.PARENT_GROUP_ID = GG.GROUP_ID(+)\n");   
		sql.append(" group by  Gg.GROUP_NAME ,t.order_type,t.order_desc\n");  
		sql.append(" order by Gg.GROUP_NAME,t.order_type asc\n");
		return this.select(SummaryReportBean.class, sql.toString(), null);
	}
}
